from comm.openDbConfig import openDbBase, dbConnectUrl
import logging
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

# 共用数据库连接
openDbBase()
# 创建对象的基类:
Base = declarative_base()
def get_yun_session(engineName=None,dbpool = None):
    # 数据库连接池
    """
    engineName:如果是多数据库连接时候则是使用具体的数据库引擎连接
    dbpool:数据库连接信息，如果不为None则通过此配置进行数据库连接
    :return:
    """
    # 获取数据库连接地址
    dbUrl, multipledb = dbConnectUrl(dbpool)
    if engineName is None:
        dbConnectAdd = dbUrl
    else:
        dbConnectAdd = multipledb[engineName]
    engine = create_engine(dbConnectAdd,
        pool_size=500,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=100  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )
    session_maker = sessionmaker(bind=engine)
    return session_maker()

# session = get_yun_session(db)
# try:
#     session.execute(text(sql),parm)
#     session.commit()
# except Exception as e:
#     session.rollback()
#     logging.info('语句执行失败'+ repr(e) + '语句' + sql +'参数'+ str(parm))
#     return None, repr(e)

# 序列化获取sql字段
def get_data_serialize(origin):
    field_li = [k[0].lower() for k in origin.cursor.description]
    return field_li
# 查询单个拼接数据方法
def get_data(field_li,data,type_z="pk"):
    if type_z!="pk":
        result_list = list()
        for itm in data:
            result = dict(zip(field_li, itm))
            result_list.append(result)
        if len(result_list)==0:
            logging.info('没查询到数据list')
            return None,"没查询到数据list"
        return result_list,None
    if data == None:
        logging.info('没查询到数据pk')
        return None, "没查询到数据pk"
    result_obj = dict(zip(field_li, data))
    return result_obj,None

# 查询使用示例
# session = get_yun_session(dbData['ERP'])
# sql = "select top 50 prd_no,name from prdt"
# parm = {}
# data = {}
# try:
#     rest = session.execute(text("select top 50 prd_no,name from prdt where prd_no='aaaa'"), parm)
#     field_li = get_data_serialize(rest)
#     data = rest.fetchone() #查询单个
#     data = rest.fetchone() #查询列表
#     result = get_data(field_li,data,"pk")
#     if result == None:
#         print("没有查询到数据")
#     print(result)
# except Exception as e:
#     logging.info('语句执行失败1' + repr(e) + '语句' + sql + '参数' + str(parm))
#     print(repr(e))

# 编辑数据
def dbEdit(sql,engineName=None,parm=None,db_session=None):
    if db_session!=None:
        session = db_session
    else:
        session = get_yun_session(engineName)
    try:
        session.execute(text(sql),parm)
        session.commit()
    except Exception as e:
        session.rollback()
        session.close()
        logging.info('语句执行失败'+ repr(e) + '语句' + sql +'参数'+ str(parm))
        return None, repr(e)
    return None,None

# 查询列表数据
def dbListJon(sql,engineName=None,parm=None,db_session=None):
    if db_session != None:
        session = db_session
    else:
        session = get_yun_session(engineName)
    try:
        rest = session.execute(text(sql),parm)
    except Exception as e:
        session.close()
        logging.info('语句执行失败' + repr(e) + '语句' + sql + '参数' + str(parm))
        return [],repr(e)

    # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
    # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
    field_li = [k[0].lower() for k in rest.cursor.description]
    # 所有数据
    try:
        data_all = rest.fetchall()  # 查询所有数据，fetchall()或fetchone()要放到description后面，否则会报错
    except Exception as e:
        session.close()
        logging.info('语句执行失败' + repr(e) + '语句' + sql + '参数' + str(parm))
        return [],repr(e)

    if data_all is None:
        logging.info('列表None值未知错误')
        return [], 'None值未知错误'
    if len(data_all)==0:
        logging.info('列表没有查询到数据')
        return [],None
    result_list = list()
    for data in data_all:
        result = dict(zip(field_li, data))
        result_list.append(result)
    return result_list,None

def dbJonOne(sql,engineName=None,parm=None,db_session=None):
    if db_session != None:
        session = db_session
    else:
        session = get_yun_session(engineName)
    try:
        rest = session.execute(text(sql), parm)
    except Exception as e:
        session.close()
        logging.info('语句执行失败1' + repr(e) + '语句' + sql + '参数' + str(parm))
        return None, repr(e)

    # 游标对象的description属性得到由字段和其他值组成的元组，格式：((字段1，...),(字段2，...)...)
    # 通过列表推导式，遍历元组，取遍历后的元组的第一个索引的值组成一个有字段组成的列表
    field_li = [k[0] for k in rest.cursor.description]
    # 所有数据
    try:
        data = rest.fetchone()
    except Exception as e:
        session.close()
        logging.info('语句执行失败2' + repr(e) + '语句' + sql + '参数' + str(parm))
        return None, repr(e)

    if data is None:
        logging.info('单个没有查询到数据')
        return None, None
    result = dict(zip(field_li, data))
    return result, None